﻿using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Text;
using PPI.Entities.Distribution;
using System.Data;
using PPI.Mobile.Logic;
using VMind.Global;
using PPI.General;

namespace PPI.Client.Logic.Distribution
{
    public class LRecloser
    {
        public static List<ERecloser> Save(List<ERecloser> reclosers, Int32 poleId)
        {
            return WebConsumer.Distribution.WPole.Save(poleId, reclosers);
        }

       
        public static List<ERecloser> GetPoleReclosers(EDistributionPole distributionPole, string connectionString)
        {
            var _sql = new StringBuilder();

            #region Query

            _sql.AppendLine("SELECT ");
            _sql.AppendLine("   R.RecloserServerId, ");
            _sql.AppendLine("   R.RecloserId, ");
            _sql.AppendLine("   R.Latitude, ");
            _sql.AppendLine("   R.Longitude, ");
            _sql.AppendLine("   R.CreatedDate, ");
            _sql.AppendLine("   R.Manufacturer, ");
            _sql.AppendLine("   R.SerialNumber, ");
            _sql.AppendLine("   R.ManufDate, ");
            _sql.AppendLine("   R.PoleNumber, ");
            _sql.AppendLine("   R.Phase, ");
            _sql.AppendLine("   R.MaxkV, ");
            _sql.AppendLine("   R.Type, ");
            _sql.AppendLine("   R.CurveA, ");
            _sql.AppendLine("   R.CurveB, ");
            _sql.AppendLine("   R.CurveC, ");
            _sql.AppendLine("   R.CurveD, ");
            _sql.AppendLine("   R.Interrupter, ");
            _sql.AppendLine("   R.Pre1978, ");
            _sql.AppendLine("   R.PCBType, ");
            _sql.AppendLine("   R.PPM, ");
            _sql.AppendLine("   R.DeterminedBy, ");
            _sql.AppendLine("   R.TestNo, ");
            _sql.AppendLine("   R.Sticker, ");
            _sql.AppendLine("   R.Comments ");
            _sql.AppendLine("FROM tb_Reclosers R ");
            _sql.AppendLine("WHERE R.Pole = " + distributionPole.Id + ";");

            #endregion

            DataTable dt = LMobileDatabase.ExecuteQueryDataset(_sql.ToString(), connectionString).Tables[0];

            var lstReclosers = new List<ERecloser>();

            foreach (DataRow dr in dt.Rows)
            {
                var poleRecloser = new ERecloser();

                poleRecloser.Id = ConvertHelper.ToInt32(dr["RecloserId"]);
                poleRecloser.ServerId = ConvertHelper.ToInt32(dr["RecloserServerId"]);
                poleRecloser.Latitude = ConvertHelper.Todouble(dr["Latitude"]);
                poleRecloser.Longitude = ConvertHelper.Todouble(dr["Longitude"]);
                poleRecloser.CreatedDate = ConvertHelper.ToDateTime(dr["CreatedDate"]);

                if (dr["Manufacturer"] != DBNull.Value)
                    poleRecloser.Manufacturer = ConvertHelper.Tostring(dr["Manufacturer"]);

                if (dr["SerialNumber"] != DBNull.Value)
                    poleRecloser.SerialNumber = dr["SerialNumber"].ToString();

                if (dr["ManufDate"] != DBNull.Value)
                    poleRecloser.ManufDate = ConvertHelper.ToDateTime(dr["ManufDate"]);

                if (dr["PoleNumber"] != DBNull.Value)
                    poleRecloser.PoleNumber = ConvertHelper.Tostring(dr["PoleNumber"]);

                if (dr["Phase"] != DBNull.Value)
                {
                    poleRecloser.Phase = new EDistributionLookUp();
                    poleRecloser.Phase.Id = ConvertHelper.ToInt32(dr["Phase"]);
                }

                if (dr["MaxkV"] != DBNull.Value)
                    poleRecloser.MaxkV = ConvertHelper.Tostring(dr["MaxkV"]);

                if (dr["Type"] != DBNull.Value)
                    poleRecloser.Type = ConvertHelper.Tostring(dr["Type"]);

                if (dr["CurveA"] != DBNull.Value)
                    poleRecloser.CurveA = ConvertHelper.ToInt32(dr["CurveA"]);

                if (dr["CurveB"] != DBNull.Value)
                    poleRecloser.CurveB = ConvertHelper.ToInt32(dr["CurveB"]);

                if (dr["CurveC"] != DBNull.Value)
                    poleRecloser.CurveC = ConvertHelper.ToInt32(dr["CurveC"]);

                if (dr["CurveD"] != DBNull.Value)
                    poleRecloser.CurveD = ConvertHelper.ToInt32(dr["CurveD"]);

                if (dr["Interrupter"] != DBNull.Value)
                    poleRecloser.Interrupter.Id = ConvertHelper.ToInt32(dr["Interrupter"]);

                if (dr["Pre1978"] != DBNull.Value)
                    poleRecloser.Pre1978 = ConvertHelper.Tobool(dr["Pre1978"]);

                if (dr["PCBType"] != DBNull.Value)
                    poleRecloser.PCBType.Id = ConvertHelper.ToInt32(dr["PCBType"]);

                if (dr["PPM"] != DBNull.Value)
                    poleRecloser.PPM = ConvertHelper.Tostring(dr["PPM"]);

                if (dr["DeterminedBy"] != DBNull.Value)
                    poleRecloser.DeterminedBy.Id = ConvertHelper.ToInt32(dr["DeterminedBy"]);

                if (dr["TestNo"] != DBNull.Value)
                    poleRecloser.TestNumber = ConvertHelper.Tostring(dr["TestNo"]);

                if (dr["Sticker"] != DBNull.Value)
                    poleRecloser.Sticker = ConvertHelper.Tobool(dr["Sticker"]);

                if (dr["Comments"] != DBNull.Value)
                    poleRecloser.Comments = ConvertHelper.Tostring(dr["Comments"]);


                poleRecloser.Images = LImage.GetRecloserImages(poleRecloser.Id, connectionString);

                _sql = new StringBuilder();

                _sql.AppendLine("SELECT ");
                _sql.AppendLine(" idKeyword  ");
                _sql.AppendLine("FROM tb_Reclosers_Keywords ");
                _sql.AppendLine("WHERE idRecloser = " + poleRecloser.Id);

                dt = LMobileDatabase.ExecuteQueryDataset(_sql.ToString(), connectionString).Tables[0];
                poleRecloser.Notes = new List<EDistributionLookUp>();
                foreach (DataRow dr2 in dt.Rows)
                {
                    var edl = new EDistributionLookUp();

                    if (dr2["idKeyword"] != DBNull.Value)
                        edl.Id = ConvertHelper.ToInt32(dr2["idKeyword"]);

                    poleRecloser.Notes.Add(edl);
                }

                lstReclosers.Add(poleRecloser);
            }

            return lstReclosers;
        }


        public static ERecloser Update(string connectionString, EDistributionPole pole, ERecloser recloser)
        {
            var query = new StringBuilder();
            var listParameters = new List<SqlParameter>();

            try
            {
                #region - Pole UPDATE -

                query.AppendLine("UPDATE tb_Reclosers SET ");
                query.AppendLine("Latitude = @Latitude,");
                query.AppendLine("Longitude = @Longitude,");
                query.AppendLine("Pole = @Pole,");
                query.AppendLine("Manufacturer = @Manufacturer, ");
                query.AppendLine("SerialNumber = @SerialNumber, ");
                query.AppendLine("ManufDate = @ManufDate,");
                query.AppendLine("PoleNumber = @PoleNumber,");
                query.AppendLine("Phase = @Phase,");
                query.AppendLine("MaxkV = @MaxkV,");
                query.AppendLine("Type = @Type,");
                query.AppendLine("CurveA = @CurveA,");
                query.AppendLine("CurveB = @CurveB,");
                query.AppendLine("CurveC = @CurveC,");
                query.AppendLine("CurveD = @CurveD,");
                query.AppendLine("Interrupter = @Interrupter,");
                query.AppendLine("Pre1978 = @Pre1978,");
                query.AppendLine("PCBType = @PCBType, ");
                query.AppendLine("PPM = @PPM, ");
                query.AppendLine("DeterminedBy = @DeterminedBy,");
                query.AppendLine("TestNo = @TestNo,");
                query.AppendLine("Sticker = @Sticker, ");
                query.AppendLine("Comments = @Comments ");
                query.AppendLine("WHERE RecloserId = @RecloserId ");

                listParameters.Add(DBHelper.CreateIntParameter("@RecloserId", ParameterDirection.Input, false, recloser.Id));

                if (recloser.Latitude != Double.MinValue)
                    listParameters.Add(DBHelper.CreateDecimalParameter("@Latitude", ParameterDirection.Input, false, 19, 6, recloser.Latitude));
                else
                    listParameters.Add(DBHelper.CreateDecimalParameter("@Latitude", ParameterDirection.Input, false, 19, 6, DBNull.Value));

                if (recloser.Longitude != Double.MinValue)
                    listParameters.Add(DBHelper.CreateDecimalParameter("@Longitude", ParameterDirection.Input, false, 19, 6, recloser.Longitude));
                else
                    listParameters.Add(DBHelper.CreateDecimalParameter("@Longitude", ParameterDirection.Input, false, 19, 6, DBNull.Value));

                listParameters.Add(DBHelper.CreateIntParameter("@Pole", ParameterDirection.Input, false, pole.Id));

                if (!string.IsNullOrEmpty(recloser.Manufacturer))
                    listParameters.Add(DBHelper.CreateNVarCharParameter("@Manufacturer", 100, ParameterDirection.Input, false, recloser.Manufacturer));
                else
                    listParameters.Add(DBHelper.CreateNVarCharParameter("@Manufacturer", 100, ParameterDirection.Input, false, DBNull.Value));

                if (!string.IsNullOrEmpty(recloser.SerialNumber))
                    listParameters.Add(DBHelper.CreateNVarCharParameter("@SerialNumber", 100, ParameterDirection.Input, false, recloser.SerialNumber));
                else
                    listParameters.Add(DBHelper.CreateNVarCharParameter("@SerialNumber", 100, ParameterDirection.Input, false, DBNull.Value));

                if (recloser.ManufDate != DateTime.MinValue)
                    listParameters.Add(DBHelper.CreateDateTimeParameter("@ManufDate", ParameterDirection.Input, false, recloser.ManufDate));
                else
                    listParameters.Add(DBHelper.CreateDateTimeParameter("@ManufDate", ParameterDirection.Input, false, DBNull.Value));

                if (!string.IsNullOrEmpty(recloser.PoleNumber))
                    listParameters.Add(DBHelper.CreateNVarCharParameter("@PoleNumber", 100, ParameterDirection.Input, false, recloser.PoleNumber));
                else
                    listParameters.Add(DBHelper.CreateNVarCharParameter("@PoleNumber", 100, ParameterDirection.Input, false, DBNull.Value));

                if (recloser.Phase != null && recloser.Phase.Id != int.MinValue && recloser.Phase.Id > 0)
                    listParameters.Add(DBHelper.CreateIntParameter("@Phase", ParameterDirection.Input, false, recloser.Phase.Id));
                else
                    listParameters.Add(DBHelper.CreateIntParameter("@Phase", ParameterDirection.Input, false, DBNull.Value));

                if (recloser.MaxkV != string.Empty)
                    listParameters.Add(DBHelper.CreateNVarCharParameter("@MaxkV", 100, ParameterDirection.Input, false, recloser.MaxkV));
                else
                    listParameters.Add(DBHelper.CreateNVarCharParameter("@MaxkV", 100, ParameterDirection.Input, false, DBNull.Value));

                if (recloser.Type != string.Empty)
                    listParameters.Add(DBHelper.CreateNVarCharParameter("@Type", 100, ParameterDirection.Input, false, recloser.Type));
                else
                    listParameters.Add(DBHelper.CreateNVarCharParameter("@Type", 100, ParameterDirection.Input, false, DBNull.Value));

                if (recloser.CurveA != int.MinValue)
                    listParameters.Add(DBHelper.CreateIntParameter("@CurveA", ParameterDirection.Input, false, recloser.CurveA));
                else
                    listParameters.Add(DBHelper.CreateIntParameter("@CurveA", ParameterDirection.Input, false, DBNull.Value));

                if (recloser.CurveB != int.MinValue)
                    listParameters.Add(DBHelper.CreateIntParameter("@CurveB", ParameterDirection.Input, false, recloser.CurveB));
                else
                    listParameters.Add(DBHelper.CreateIntParameter("@CurveB", ParameterDirection.Input, false, DBNull.Value));

                if (recloser.CurveC != int.MinValue)
                    listParameters.Add(DBHelper.CreateIntParameter("@CurveC", ParameterDirection.Input, false, recloser.CurveC));
                else
                    listParameters.Add(DBHelper.CreateIntParameter("@CurveC", ParameterDirection.Input, false, DBNull.Value));

                if (recloser.CurveD != int.MinValue)
                    listParameters.Add(DBHelper.CreateIntParameter("@CurveD", ParameterDirection.Input, false, recloser.CurveD));
                else
                    listParameters.Add(DBHelper.CreateIntParameter("@CurveD", ParameterDirection.Input, false, DBNull.Value));

                if (recloser.Interrupter != null && recloser.Interrupter.Id != int.MinValue && recloser.Interrupter.Id > 0)
                    listParameters.Add(DBHelper.CreateIntParameter("@Interrupter", ParameterDirection.Input, false, recloser.Interrupter.Id));
                else
                    listParameters.Add(DBHelper.CreateIntParameter("@Interrupter", ParameterDirection.Input, false, DBNull.Value));

                listParameters.Add(DBHelper.CreateBitParameter("@Pre1978", ParameterDirection.Input, false, recloser.Pre1978));

                if (recloser.PCBType != null && recloser.PCBType.Id != int.MinValue && recloser.PCBType.Id > 0)
                    listParameters.Add(DBHelper.CreateIntParameter("@PCBType", ParameterDirection.Input, false, recloser.PCBType.Id));
                else
                    listParameters.Add(DBHelper.CreateIntParameter("@PCBType", ParameterDirection.Input, false, DBNull.Value));

                if (!string.IsNullOrEmpty(recloser.PPM))
                    listParameters.Add(DBHelper.CreateNVarCharParameter("@PPM", 100, ParameterDirection.Input, false, recloser.PPM));
                else
                    listParameters.Add(DBHelper.CreateNVarCharParameter("@PPM", 100, ParameterDirection.Input, false, DBNull.Value));

                if (recloser.DeterminedBy != null && recloser.DeterminedBy.Id != int.MinValue && recloser.DeterminedBy.Id > 0)
                    listParameters.Add(DBHelper.CreateIntParameter("@DeterminedBy", ParameterDirection.Input, false, recloser.DeterminedBy.Id));
                else
                    listParameters.Add(DBHelper.CreateIntParameter("@DeterminedBy", ParameterDirection.Input, false, DBNull.Value));

                if (!string.IsNullOrEmpty(recloser.TestNumber))
                    listParameters.Add(DBHelper.CreateNVarCharParameter("@TestNo", 100, ParameterDirection.Input, false, recloser.TestNumber));
                else
                    listParameters.Add(DBHelper.CreateNVarCharParameter("@TestNo", 100, ParameterDirection.Input, false, DBNull.Value));

                listParameters.Add(DBHelper.CreateBitParameter("@Sticker", ParameterDirection.Input, false, recloser.Sticker));

                if (recloser.Comments != string.Empty)
                    listParameters.Add(DBHelper.CreateNVarCharParameter("@Comments", 512, ParameterDirection.Input, false, recloser.Comments));
                else
                    listParameters.Add(DBHelper.CreateNVarCharParameter("@Comments", 512, ParameterDirection.Input, false, DBNull.Value));


                LMobileDatabase.ExecuteSingleQueryWhithParameters(query.ToString(), connectionString, listParameters.ToArray());

                #endregion

                #region Update Notes

                query = new StringBuilder("DELETE tb_Reclosers_Keywords WHERE idRecloser = @idRecloser");
                listParameters.Clear();
                listParameters.Add(DBHelper.CreateIntParameter("@idRecloser", ParameterDirection.Input, false, recloser.Id));
                LMobileDatabase.ExecuteSingleQueryWhithParameters(query.ToString(), connectionString, listParameters.ToArray());

                foreach (EDistributionLookUp note in recloser.Notes)
                {
                    query = new StringBuilder("INSERT INTO tb_Reclosers_Keywords (idRecloser, idKeyword) VALUES (");
                    query.AppendLine("@idRecloser, @idKeyword)");

                    listParameters.Clear();
                    listParameters.Add(DBHelper.CreateIntParameter("@idRecloser", ParameterDirection.Input, false, recloser.Id));
                    listParameters.Add(DBHelper.CreateIntParameter("@idKeyword", ParameterDirection.Input, false, note.Id));

                    LMobileDatabase.ExecuteSingleQueryWhithParameters(query.ToString(), connectionString, listParameters.ToArray());
                }

                #endregion

                #region - Image UPDATE -

                query = new StringBuilder("DELETE tb_RecloserImages WHERE idRecloser = @idRecloser");
                listParameters.Clear();
                listParameters.Add(DBHelper.CreateIntParameter("@idRecloser", ParameterDirection.Input, false, recloser.Id));
                LMobileDatabase.ExecuteSingleQueryWhithParameters(query.ToString(), connectionString, listParameters.ToArray());

                int imagesCount = 0;

                foreach (EDistributionPoleImage image in recloser.Images)
                {
                    query = new StringBuilder("INSERT INTO tb_RecloserImages (idRecloser, FileName) VALUES (");
                    query.AppendLine("@idRecloser, @FileName)");

                    image.GenerateNewName(recloser.Id.ToString() + imagesCount);
                    listParameters.Clear();
                    listParameters.Add(DBHelper.CreateIntParameter("@idRecloser", ParameterDirection.Input, false, recloser.Id));
                    listParameters.Add(DBHelper.CreateNVarCharParameter("@FileName", 150, ParameterDirection.Input, false, image.FileName));

                    LMobileDatabase.ExecuteSingleQueryWhithParameters(query.ToString(), connectionString, listParameters.ToArray());
                    SaveImage(image, connectionString);
                    imagesCount++;
                }

                #endregion


                return recloser;
            }
            catch (Exception exc)
            {
                throw exc;
            }
        }

        public static void UpdateUploadedData(List<ERecloser> reclosers, Int32 poleId, string connectionString)
        {
            foreach (ERecloser recloser in reclosers)
            {
                var _query = new StringBuilder();
                _query.AppendLine("UPDATE tb_Reclosers set RecloserServerID = " + recloser.ServerId + ", PoleServerID = " + poleId + ", UploadedDate = " +
                                  DateTime.Now.ToString("yyyy-MM-dd") + " where RecloserId = " + recloser.Id + " ");

                LMobileDatabase.ExecuteQuery(_query.ToString(), connectionString);
            }
        }

        private static Int32 SaveImage(EDistributionPoleImage image, string connectionString)
        {
            Int32 returnId = image.Id;

            if (image.Id == Int32.MinValue)
            {
                string newImageName = image.FileName;
                DirHelper.CopyImageToOutbox(connectionString, image.FullName, newImageName);
            }

            return returnId;
        }

        public static void Delete(string connectionString, ERecloser recloser)
        {
            var listParameters = new List<SqlParameter>();
            var query = new StringBuilder();

            try
            {
                query.AppendLine("DELETE tb_Reclosers_Keywords ");
                query.AppendLine("WHERE idRecloser = @idRecloser ");

                listParameters.Add(DBHelper.CreateIntParameter("@idRecloser", ParameterDirection.Input, false, recloser.Id));

                LMobileDatabase.ExecuteSingleQueryWhithParameters(query.ToString(), connectionString, listParameters.ToArray());

                query.AppendLine("DELETE tb_RecloserImages ");
                query.AppendLine("WHERE idRecloser = @idRecloser ");

                query = new StringBuilder();
                listParameters = new List<SqlParameter>();
                listParameters.Add(DBHelper.CreateIntParameter("@idRecloser", ParameterDirection.Input, false, recloser.Id));

                LMobileDatabase.ExecuteSingleQueryWhithParameters(query.ToString(), connectionString, listParameters.ToArray());

                query = new StringBuilder();
                query.AppendLine("DELETE tb_Reclosers ");
                query.AppendLine("WHERE idRecloser = @idRecloser ");

                listParameters = new List<SqlParameter>();
                listParameters.Add(DBHelper.CreateIntParameter("@idRecloser", ParameterDirection.Input, false, recloser.Id));

                LMobileDatabase.ExecuteSingleQueryWhithParameters(query.ToString(), connectionString, listParameters.ToArray());
            }
            catch (Exception exc)
            {
                throw exc;
            }
        }

        public static List<EDistributionLookUp> SaveRecloserNotes(List<EDistributionLookUp> notes, Int32 recloserId)
        {
            return WebConsumer.Distribution.WPole.SaveRecloserNotes(recloserId, notes);
        }
    }
}
